#!/usr/bin/env python
# -*- coding: utf-8 -*-
#利用mysql binlog实现dml语句监控，故障回滚
#
# Dump all replication events from a remote mysql server
#
import myClass
import pymysql,datetime
from pymysqlreplication import BinLogStreamReader
from pymysqlreplication.event import (
    QueryEvent, RotateEvent, FormatDescriptionEvent,
    XidEvent, GtidEvent, StopEvent,
    BeginLoadQueryEvent, ExecuteLoadQueryEvent,
    HeartbeatLogEvent, NotImplementedEvent)
from pymysqlreplication.row_event import (
    UpdateRowsEvent, WriteRowsEvent, DeleteRowsEvent, TableMapEvent)

#
MYSQL_SETTINGS_pro_zcode = {
    "host": "",
    "port": 3306,
    "user": "",
    "passwd": "",
}

LOGS_INFO_pro_zcode = {
    "log_pos":123456,
    "log_file":'mysql.000018'
}

#
MYSQL_SETTINGS_pro_if = {
    "host": "",
    "port": 3306,
    "user": "repl",
    "passwd": "123456"
}

LOGS_INFO_pro_if = {
    "log_pos" : 680885632,
    "log_file" : 'mysql-bin.000107'
}

#192.168.5.244
MYSQL_SETTINGS_sit_if = {
    "host": "",
    "port": 3306,
    "user": "repl",
    "passwd": "123456"
}

LOGS_INFO_sit_if = {
    "log_pos" : 648796111,
    "log_file" : 'mysql-bin.000145'
}

host='192.168.5.211'
user='root'
password='123456'
port=3306

def create_tb(conn):
    cursor = conn.cursor()
    create_tb_sql = """
        CREATE TABLE IF NOT EXISTS test.t_binlog_event (
            auto_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
            binlog_name VARCHAR(100) NOT NULL ,
            dml_start_time DATETIME NOT NULL,
            dml_end_time DATETIME NOT NULL,
            start_log_pos BIGINT NOT NULL,
            end_log_pos BIGINT NOT NULL,
            db_name VARCHAR(100) NOT NULL ,
            table_name VARCHAR(200) NOT NULL ,
            dml_sql LONGTEXT NULL ,
            undo_sql LONGTEXT NULL ,
            PRIMARY KEY (auto_id),
            INDEX dml_start_time (dml_start_time),
            INDEX dml_end_time (dml_end_time),
            INDEX start_log_pos (start_log_pos),
            INDEX end_log_pos (end_log_pos),
            INDEX db_name (db_name),
            INDEX table_name (table_name)
        )
        COLLATE='utf8_general_ci' ENGINE=InnoDB;
        """
    cursor.execute(create_tb_sql)
    conn.commit()

def insert_format(table,dic):
    result = []
    for dic_ in dic:
        keys = dic_['values'].keys()
        values = dic_['values'].values()
        p_k = ''
        for k in keys:
            p_k = p_k + "`%s`" % k +","
        p_k = p_k[:-1]
        p_v = ''
        for v in values:
            if type(v)==type(1):
                 p_v = p_v + "%d" % v +","
            if type(v) == type(u'a'):
                 vl = v
                 p_v = p_v + "'%s'" % vl +","
        p_v = p_v[:-1]
        p = {'key':p_k,'value':p_v}
        sql = "insert into %s (%s) value (%s)" % (table,p['key'],p['value'])
        result.append(sql)
    return result


def delete_format(table,dic):
    result = []
    for dic_ in dic:
        value = dic_['values']
        keys = value.keys()
        p_k_v = ''
        for p in keys:
            if type(value[p])==type(1):
                p_k_v = p_k_v + "`%s`=%d and " % (p,value[p])
            if type(value[p])==type(u'a'):
                vl = value[p]
                p_k_v = p_k_v + u"`%s`='%s' and " % (p,vl)
        p_k_v = p_k_v[:-4]
        sql = u"delete from %s where %s " % (table,p_k_v)
        result.append(sql)
    return result

def update_format(table,dic,ty):
    result = []
    for dic_ in dic:
        value = dic_
        before_values = value['before_values']
        after_values = value['after_values']
        keys_b = before_values.keys()
        p_k_v_b = ''
        if ty == 'dml':
            for p in keys_b:
                if type(before_values[p])==type(1):
                    p_k_v_b = p_k_v_b + "`%s`=%d and " % (p,before_values[p])
                if type(before_values[p])==type(u'a'):
                    vl = before_values[p]
                    p_k_v_b = p_k_v_b + "`%s`='%s' and " % (p,vl)
            p_k_v_b = p_k_v_b[:-4]
            keys_a = after_values.keys()
            p_k_v_a = ''
            for p in keys_a:
                if type(after_values[p])==type(1):
                    p_k_v_a = p_k_v_a + "`%s`=%d ," % (p,after_values[p])
                if type(after_values[p])==type(u'a'):
                    vl = after_values[p]
                    p_k_v_a = p_k_v_a + "`%s`='%s'," % (p,vl)
            p_k_v_a = p_k_v_a[:-1]
            p = {'before':p_k_v_b,'after':p_k_v_a}
            sql = "update %s set %s where %s" % (table,p['before'],p['after'])
            result.append(sql)
        else :
            for p in keys_b:
                if type(before_values[p])==type(1):
                    p_k_v_b = p_k_v_b + "`%s`=%d ," % (p,before_values[p])
                if type(before_values[p])==type(u'a'):
                    vl = before_values[p]
                    p_k_v_b = p_k_v_b + "`%s`='%s'," % (p,vl)
            p_k_v_b = p_k_v_b[:-1]
            keys_a = after_values.keys()
            p_k_v_a = ''
            for p in keys_a:
                if type(after_values[p])==type(1):
                    p_k_v_a = p_k_v_a + "`%s`=%d and " % (p,after_values[p])
                if type(after_values[p])==type(u'a'):
                    #print after_values[p].encode('utf-8')
                    vl = after_values[p]
                    p_k_v_a = p_k_v_a + "`%s`='%s'and " % (p,vl)
            p_k_v_a = p_k_v_a[:-4]
            p = {'before':p_k_v_b,'after':p_k_v_a}
            sql = "update %s set %s where %s" % (table,p['after'],p['before'])
            result.append(sql)
    return result

def writeLog(file,log):
    f = file
    file_object = open(f,'a')
    try:
        file_object.write(log)
    finally:
        file_object.close( )

def main():
    #conn = myClass.MySQLHelper(host=strHost, db=strDB, user=strUser, passwd=strPasswd, port=port, charset="utf8")

    # server_id is your slave identifier, it should be unique.
    # set blocking to True if you want to block and wait for the next event at
    # the end of the stream
    stream = BinLogStreamReader(connection_settings=MYSQL_SETTINGS_sit_if,resume_stream=True,log_pos=LOGS_INFO_sit_if['log_pos'],log_file=LOGS_INFO_sit_if['log_file'],
                                server_id=203,only_events=(UpdateRowsEvent, WriteRowsEvent, DeleteRowsEvent),fail_on_table_metadata_unavailable=True,freeze_schema=True,
                                only_schemas=('iflashbuy'),
                                only_tables=('t_property_base'),
                                #only_tables=(['t_category_attribute', 't_attribute_base', 't_attribute_values']),
                                blocking=True)
    incont = 0
    upcont = 0
    delcont = 0
    incont_ = 0
    upcont_ = 0

    for binlogevent in stream:
        table = binlogevent.table
        next_log_pos = binlogevent.packet.log_pos
        ervent_time = datetime.datetime.fromtimestamp(binlogevent.packet.timestamp).isoformat()
        #print(binlogevent.rows)

        print "事务开始##########"
        print "下一个pos:%d" % (next_log_pos)
        #print binlogevent.event_type
        if binlogevent.event_type == 30:
            row = binlogevent.rows
            #print row
            res = insert_format(table,row)
            dml_in_sql = res
            res = delete_format(table,row)
            undo_sql = res
            print u"++++发现insert操作%d,表%s++++" % (incont,table)
            print "dml_sql语句:"
            for resinsql in dml_in_sql:
                print resinsql.encode('utf-8')
            print "undo_sql语句:"
            for resundo_sql in undo_sql:
                print resundo_sql.encode('utf-8')


        if binlogevent.event_type == 31:
            upcont = upcont+1
            row = binlogevent.rows
            res = update_format(table,row,ty='dml')
            dml_up_sql = res
            res = update_format(table,row,ty='undo')
            undo_sql = res
            print u"++++发现update%d操作,表%s++++" % (upcont,table)
            '''
            print u"dml_sql语句:"
            for resupsql in dml_up_sql:
                print resupsql.encode('utf-8')
           '''
            print u"undo_sql语句:"
            for resundosql in undo_sql:
                print resundosql.encode('utf-8')

        if binlogevent.event_type == 32:
            delcont = delcont+1
            row = binlogevent.rows
            res = insert_format(table,row)
            undo_sql = res
            res = delete_format(table,row)
            dml_sql = res
            print u"++++发现删除操作%d,表%s++++" % (delcont,table)
            '''
            print u"dml_sql语句:"
            for resdmlsql in dml_sql:
                print resdmlsql.encode('utf-8')
           '''
            #print u"undo_sql语句:"
            for resundosql in undo_sql:
                print resundosql.encode('utf-8')

    stream.close()

if __name__ == "__main__":
    main()
